<?php
    $this->PhpExcel->Excel();
    $this->PhpExcel->titulos_dinamicos(array(
            'A8' => 'Datos del Cliente:',
            'D6' => 'RELACION DE VIAJES',
            'D7' => 'TRANSPORTE AFILIADO AGROPATRIA',
            'A9' => 'Razón Social:',
            'C9' => $data['Afiliado']['nombre'],
            'A10' => 'Dirección Fiscal:',
            'C10' => $data['Afiliado']['direccion'],
            'A11' => 'RIF:',
            'C11' => $data['Afiliado']['ced_rif'],
            'A12' => 'Teléfono:',
            'C12' => $data['Afiliado']['telf'],
            'A13' => 'Número de Factura:',
            'C13' => "Nº {$data['Orden']['nro_factura']}",
            'A14' => 'Número de Control:',
            'C14' => "Nº {$data['Orden']['nro_control']}",
            'J4' => "Fecha: {$this->Interfaz->fecha($data['Orden']['fecha'])}",
            'J5' => "AF-RV-{$data['Orden']['codigo']}"
            ));
    
    $ext = ($tipo == 'e') ? 'xlsx' : 'ods';
    
    $nombre = "RELACION_DE_VIAJES" . date("d_m_Y");
    $this->PhpExcel->titulo_hoja($data['Afiliado']['ced_rif']);
    
    $this->PhpExcel->Imagen('Agropatria', 'Logo Agropatria', WWW_ROOT . 'img/logo_agropatria.png', 'A2', array('width' => 300, 'offsetX' => 30));
    $this->PhpExcel->Imagen('Ministerio', 'Logo Ministerios', WWW_ROOT . 'img/logo_ministerio.png', 'H2', array('width' => 180));

    $this->PhpExcel->hoja->mergeCells('D6:G6');
    $this->PhpExcel->hoja->mergeCells('D7:G7');
    
    $this->PhpExcel->hoja->getDefaultStyle()->applyFromArray(array('font' => array('name' => 'arial', 'size' => 10)));
    $this->PhpExcel->hoja->getStyle('D6:D7')->applyFromArray(array('font' => array('bold' => true, 'size' => 15), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)));
    $this->PhpExcel->hoja->getStyle('A8:A14')->applyFromArray(array('font' => array('bold' => true, 'size' => 12)));
    $this->PhpExcel->hoja->getStyle('C8:C14')->applyFromArray(array('font' => array('bold' => false, 'size' => 12)));

    $arrStyleTitle = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,
                                'startcolor' => array('argb' => '81DAF5')),
                            'font' => array('bold' => true,
                                'color' => array('argb' => PHPExcel_Style_Color::COLOR_WHITE)),
                            'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
    
    $arrStyleTitleuno = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,
                                    'startcolor' => array('argb' => 'EFFBFB')));
    
    $arrTotales = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,),
                        'font' => array('bold' => true,
                            'color' => array('argb' => PHPExcel_Style_Color::COLOR_BLACK)));

    $arrborderTitleuno = array('style' => PHPExcel_Style_Border::BORDER_DASHDOT, 'color' => array('argb' => '000000'));
    $arrTotalesBorde = array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('argb' => '000000'));

    $this->PhpExcel->configura_celda('A', 10); //NRO
    $this->PhpExcel->configura_celda('B', 20); //GUIA
    $this->PhpExcel->configura_celda('C', 15); //FECHA
    $this->PhpExcel->configura_celda('D', 40); //ORIGEN Y DESTINO
    $this->PhpExcel->configura_celda('E', 10); //PESO
    $this->PhpExcel->configura_celda('F', 15); //NRO DE SACOS
    $this->PhpExcel->configura_celda('G', 40); //CARGA
    $this->PhpExcel->configura_celda('H', 15); //BS TONELADA
    $this->PhpExcel->configura_celda('I', 30); //CHOFER
    $this->PhpExcel->configura_celda('J', 15); //PLACA
    $this->PhpExcel->configura_celda('K', 15); //BS FLETE
    $this->PhpExcel->configura_celda('L', 15); //CALETA

    $index_title = 16;

    $j = 0;
    $tit[] = array('NRO', 'STR', 'C');
    $tit[] = array('GUIA', 'STR', 'C');
    $tit[] = array('FECHA', 'STR', 'C');
    $tit[] = array('ORIFEN Y DESTINO', 'STR', 'C');
    $tit[] = array('PESO', 'STR', 'C');
    $tit[] = array('NRO DE SACOS', 'STR', 'C');
    $tit[] = array('CARGA', 'STR', 'C');
    $tit[] = array('BS TONELADA', 'STR', 'C');
    $tit[] = array('CHOFER', 'STR', 'C');
    $tit[] = array('PLACA', 'STR', 'C');
    $tit[] = array('BS FLETE', 'STR', 'J');
    $tit[] = array('CALETA', 'STR', 'J');
    
    foreach ($tit as $columnTitle) {
        $this->PhpExcel->valor_celda($j, $index_title, $columnTitle);
        $oColumn = $this->PhpExcel->obtener_columna($j, $index_title);
        $oRow = $this->PhpExcel->obtener_fila($j, $index_title);
        $this->PhpExcel->hoja->getStyle($oColumn . $oRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->PhpExcel->hoja->getStyle($oColumn . $oRow)->applyFromArray($arrStyleTitle);
        $this->PhpExcel->hoja->getStyle($oColumn . $oRow)->getBorders()->getAllBorders()->applyFromArray($arrborderTitleuno);
        $j++;
    }

    $fila = 17;
    
    $i = 1;
    $subTPeso = $subTSacos = $subTFlete = $subTCaleta = 0;
    
    if(!empty($conceptos)){
        foreach ($conceptos as $detalleCon){
            $listaConceptos[] = $detalleCon['ViajeConcepto']['viaje_id'];
        }
    }
    
    foreach ($resultado as $detalle){
        if(in_array($detalle['Viaje']['id'], $viajes)){
            $valores = array();
            $valores[] = array($i, 'STR', 'C');
            $valores[] = array($detalle['Viaje']['guia'] = ($detalle['Viaje']['transferencia']) ? "TRANSF - {$detalle['Viaje']['guia']}" : $detalle['Viaje']['guia'], 'STR', 'C');
            $valores[] = array($this->Interfaz->fecha($detalle['Viaje']['fecha_carga']), 'STR', 'C');
            $valores[] = array("{$detalle['OrigenViaje']['nombre']} - {$detalle['DestinoViaje']['nombre']}", 'STR', 'C');
            $valores[] = array($detalle['Viaje']['peso'], 'N', 'R');
            $valores[] = array($detalle['Viaje']['cant_sacos'], 'N', 'R');
            $valores[] = array($detalle['Producto']['descripcion'], 'STR', 'C');
            $valores[] = array($detalle['Viaje']['precio_ton'], 'N', 'R');
            $valores[] = array($detalle['Chofer']['nombre'] = (!empty($detalle['Chofer']['nombre']) ? $detalle['Chofer']['nombre'] : '-'), 'STR', 'C');
            $valores[] = array($detalle['Viaje']['placa'] = (!empty($detalle['Viaje']['placa']) ? $detalle['Viaje']['placa'] : '-'), 'STR', 'C');
            $valores[] = array($detalle['Viaje']['flete'], 'N', 'R');
            $caleta = ($detalle['Viaje']['caleta_carga'] + $detalle['Viaje']['caleta_descarga']);
            $valores[] = array($caleta, 'N', 'R');
            
            $subTPeso += $detalle['Viaje']['peso'];
            $subTSacos += $detalle['Viaje']['cant_sacos'];
            $subTFlete += $detalle['Viaje']['flete'];
            $subTCaleta += $caleta;
            
            $columna = 0;
            
            foreach ($valores as $valor) {
                $this->PhpExcel->valor_celda($columna, $fila, $valor);
                $oColumn = $this->PhpExcel->obtener_columna($columna, $fila);
                $oRow = $this->PhpExcel->obtener_fila($columna, $fila);
                if ($fila % 2 == 0) {
                    $this->PhpExcel->hoja->getStyle($oColumn . $oRow)->applyFromArray($arrStyleTitleuno);
                }
                $this->PhpExcel->hoja->getStyle($oColumn . $oRow)->getBorders()->getAllBorders()->applyFromArray($arrborderTitleuno);
                $columna++;
            }
            $fila++;
            
            if(!empty($listaConceptos)){
                if(in_array($detalle['Viaje']['id'], $listaConceptos)){
                    $i++;
                    foreach ($conceptos as $indice => $detalleCon){
                        if($detalleCon['ViajeConcepto']['viaje_id'] == $detalle['Viaje']['id']){
                            $valoresConcepto = array();
                            $valoresConcepto[] = array($i, 'STR', 'C');
                            $valoresConcepto[] = array("{$detalleCon['Concepto']['nombre']} {$detalle['Viaje']['guia']}", 'STR', 'C');
                            $valoresConcepto[] = array('-', 'STR', 'C');
                            $valoresConcepto[] = array($detalleCon['ViajeConcepto']['descripcion'], 'STR', 'C');
                            $valoresConcepto[] = array($detalle['Viaje']['peso'], 'N', 'R');
                            $valoresConcepto[] = array($detalle['Viaje']['cant_sacos'], 'N', 'R');
                            $valoresConcepto[] = array($detalle['Producto']['descripcion'], 'STR', 'C');
                            $valoresConcepto[] = array(0, 'N', 'R');
                            $valoresConcepto[] = array($detalle['Chofer']['nombre'] = (!empty($detalle['Chofer']['nombre']) ? $detalle['Chofer']['nombre'] : '-'), 'STR', 'C');
                            $valoresConcepto[] = array($detalle['Viaje']['placa'] = (!empty($detalle['Viaje']['placa']) ? $detalle['Viaje']['placa'] : '-'), 'STR', 'C');
                            $valoresConcepto[] = array($detalleCon['ViajeConcepto']['monto'] = ($detalleCon['Concepto']['tipo'] == 1) ? $detalleCon['ViajeConcepto']['monto'] : "-{$detalleCon['ViajeConcepto']['monto']}", 'N', 'R');
                            $valoresConcepto[] = array(0, 'N', 'R');

                            $subTPeso += $detalle['Viaje']['peso'];
                            $subTSacos += $detalle['Viaje']['cant_sacos'];
                            $subTFlete += $detalleCon['ViajeConcepto']['monto'];

                            $columna = 0;

                            foreach ($valoresConcepto as $valor) {
                                $this->PhpExcel->valor_celda($columna, $fila, $valor);
                                $oColumn = $this->PhpExcel->obtener_columna($columna, $fila);
                                $oRow = $this->PhpExcel->obtener_fila($columna, $fila);
                                if ($fila % 2 == 0) {
                                    $this->PhpExcel->hoja->getStyle($oColumn . $oRow)->applyFromArray($arrStyleTitleuno);
                                }
                                $this->PhpExcel->hoja->getStyle($oColumn . $oRow)->getBorders()->getAllBorders()->applyFromArray($arrborderTitleuno);
                                $columna++;
                            }

                            $i++;
                            $fila++;
                            unset($conceptos[$indice]);
                        }
                    }
                }else
                    $i++;
            }
        }
    }
    
    $subTotales[0] = array('');
    $subTotales[1] = array('');
    $subTotales[2] = array('');
    $subTotales[3] = array('');
    $subTotales[4] = array($subTPeso, 'N', 'R');
    $subTotales[5] = array($subTSacos, 'N', 'R');
    $subTotales[6] = array('');
    $subTotales[7] = array('');
    $subTotales[8] = array('');
    $subTotales[9] = array('');
    $subTotales[10] = array($subTFlete, 'N', 'R');
    $subTotales[11] = array($subTCaleta, 'N', 'R');
    
    $columna = 0;
    
    foreach($subTotales as $valor){
        $this->PhpExcel->valor_celda($columna, $fila, $valor);
        $oColumn = $this->PhpExcel->obtener_columna($columna, $fila);
        $oRow = $this->PhpExcel->obtener_fila($columna, $fila);
        $this->PhpExcel->hoja->getStyle($oColumn . $oRow)->applyFromArray($arrTotales);
        if(count($valor) > 1)
            $this->PhpExcel->hoja->getStyle($oColumn . $oRow)->getBorders()->getAllBorders()->applyFromArray($arrTotalesBorde);
        $columna++;
    }
    
    $fila += 3;
    $desde = $fila;
    
    $iva = (!empty($data['Orden']['iva'])) ? $data['Orden']['iva'] : 0;
    $ivaFlete = (($iva * $subTFlete) / 100);
    $totalFlete = ($subTFlete + $ivaFlete);
    $totalPagar = ($totalFlete + $subTCaleta);
    
    /*$this->PhpExcel->titulos_dinamicos(array(
            "A$fila" => 'Sub-Total Flete',
            "C$fila" => $subTFlete,
            "A" . ++$fila => "IVA {$data['Orden']['iva']}% sobre Flete",
            "C$fila" => $ivaFlete,
            "A" . ++$fila => "TOTAL FLETE",
            "C$fila" => $totalFlete,
            "A" . ++$fila => "TOTAL CALETA",
            "C$fila" => $subTCaleta,
            "A" . ++$fila => "TOTAL A PAGAR",
            "C$fila" => $totalPagar,
            ));*/
            
    $this->PhpExcel->valor_celda(0, $fila, array("Sub-Total Flete"));
    $this->PhpExcel->valor_celda(2, $fila, array($subTFlete, 'N', 'R'));
    $this->PhpExcel->valor_celda(0, ++$fila, array("IVA {$data['Orden']['iva']}% sobre Flete"));
    $this->PhpExcel->valor_celda(2, $fila, array($ivaFlete, 'N', 'R'));
    $this->PhpExcel->valor_celda(0, ++$fila, array("TOTAL FLETE"));
    $this->PhpExcel->valor_celda(2, $fila, array($totalFlete, 'N', 'R'));
    $this->PhpExcel->valor_celda(0, ++$fila, array("TOTAL CALETA"));
    $this->PhpExcel->valor_celda(2, $fila, array($subTCaleta, 'N', 'R'));
    $this->PhpExcel->valor_celda(0, ++$fila, array("TOTAL A PAGAR"));
    $this->PhpExcel->valor_celda(2, $fila, array($totalPagar, 'N', 'R'));
            
    $this->PhpExcel->hoja->getStyle("A$desde:C$fila")->applyFromArray($arrTotales);
    $this->PhpExcel->hoja->getStyle("A$desde:C$fila")->getBorders()->getAllBorders()->applyFromArray($arrTotalesBorde);
    $this->PhpExcel->hoja->mergeCells("A" . $desde . ":B" . $desde++);
    $this->PhpExcel->hoja->mergeCells("A" . $desde . ":B" . $desde++);
    $this->PhpExcel->hoja->mergeCells("A" . $desde . ":B" . $desde++);
    $this->PhpExcel->hoja->mergeCells("A" . $desde . ":B" . $desde++);
    $this->PhpExcel->hoja->mergeCells("A" . $desde . ":B" . $desde);
    
    $this->PhpExcel->titulos_dinamicos(array(
            "D" . --$fila => 'ANALISTA DE PAGO',
            "D" . ++$fila => 'REALIZADO POR',
            "E" . --$fila => "COORD. AFILIADOS",
            "E" . ++$fila => "REVISADO POR",
            "G" . --$fila => "GERENCIA GENERAL",
            "G" . ++$fila => "AUTORIZADO POR",
            "H" . --$fila => "ADMINISTRACION",
            "H" . ++$fila => "RECIBIDO POR",
            ));
    
    $this->PhpExcel->hoja->mergeCells("E" . --$fila . ":F" . $fila);
    $this->PhpExcel->hoja->mergeCells("H" . $fila . ":I" . $fila);
    $this->PhpExcel->hoja->mergeCells("E" . ++$fila . ":F" . $fila);
    $this->PhpExcel->hoja->mergeCells("H" . $fila . ":I" . $fila);
    
    $this->PhpExcel->hoja->getStyle('D' . --$fila . ':H' . ++$fila)->applyFromArray(array('font' => array('size' => 12), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)));
    $this->PhpExcel->hoja->getStyle('D' . $fila . ':H' . $fila)->applyFromArray(array('font' => array('bold' => true, 'size' => 12), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)));

    $this->PhpExcel->mostrar($nombre, $ext);